package Util;

import DataClass.Student;
import DataClass.Teacher;
import Page.UserManagementPage;
import javafx.scene.control.Alert;

import java.sql.*;
import java.util.List;

/**
 * @since 2024-06-01
 * @author yooo_fan
 * 数据库工具类
 */
public class DatabaseUtil {
    private static Connection c = null;
    private static Statement stmt = null;

    public static void getConnect(){
        try{
            Class.forName("org.postgresql.Driver");
            c = DriverManager.getConnection(
                    "jdbc:postgresql://124.70.107.17:26000/postgres",
                    "py_omm",
                    "123456@abc"
            );
            stmt = c.createStatement();
            c.setAutoCommit(false);
            System.out.println("数据库连接成功");

        } catch (ClassNotFoundException | SQLException e) {
            System.err.println("数据库连接失败");
            throw new RuntimeException(e);
        }
    }

    public static String addStudent(Student student) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        // SQL 插入语句
        String sql = "INSERT INTO Student(SNo, SCname, SType, SEnter, STNo, SATNo, SRemark, SAccount, SPassword, SIn, SOut)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            // 设置 PreparedStatement 的参数
            pstmt.setString(1, student.getSNo());
            pstmt.setString(2, student.getSCName());
            pstmt.setString(3, student.getSType());
            pstmt.setInt(4, student.getSEnter());
            pstmt.setString(5, student.getSTNo());
            pstmt.setString(6, student.getSATNo());
            pstmt.setString(7, student.getSRemark());
            pstmt.setString(8, student.getSAccount());
            pstmt.setString(9, student.getSPassword());
            pstmt.setTimestamp(10, student.getSIn());
            pstmt.setTimestamp(11, student.getSOut());

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                c.commit();  // 提交事务
                return "添加成功";
            } else {
                c.rollback();  // 回滚事务
                return "添加失败：数据输入不合规范";
            }
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
            return "添加失败：数据库操作错误";
        }
    }

    public static String addTeacher(Teacher teacher) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        // SQL 插入语句
        String sql = "INSERT INTO Teacher(Tno, TTno, TCName, TSex, JTno, TQualification, TDuties, TRemark, TSituation, TEducation, TFirstDiscipline, TSceondDiscipline, TProfessionalAffiliations, TAdminPositionsCollege, TAcademicPositionsCollege)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            // 设置 PreparedStatement 的参数
            pstmt.setString(1, teacher.getTno());
            pstmt.setString(2, teacher.getTTno());
            pstmt.setString(3, teacher.getTCName());
            pstmt.setString(4, teacher.getTSex());
            pstmt.setString(5, teacher.getJTno());
            pstmt.setString(6, teacher.getTQualification());
            pstmt.setString(7, teacher.getTDuties());
            pstmt.setString(8, teacher.getTRemark());
            pstmt.setString(9, teacher.getTSituation());
            pstmt.setString(10, teacher.getTEducation());
            pstmt.setString(11, teacher.getTFirstDiscipline());
            pstmt.setString(12, teacher.getTSecondDiscipline());
            pstmt.setString(13, teacher.getTProfessionalAffiliations());
            pstmt.setString(14, teacher.getTAdminPositionsCollege());
            pstmt.setString(15, teacher.getTAcademicPositionsCollege());

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                c.commit();  // 提交事务
                return "添加成功";
            } else {
                c.rollback();  // 回滚事务
                return "添加失败：数据输入不合规范";
            }
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
            return "添加失败：数据库操作错误";
        }
    }


    public static String DeleteStudent(String SNo) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        String sql = "DELETE FROM Student WHERE SNo = '" + SNo + "'";

        try {
            int affectedRows = stmt.executeUpdate(sql);
            if (affectedRows > 0) {
                c.commit();  // 提交事务
                return "删除成功";
            } else {
                c.rollback();  // 回滚事务
                return "删除失败：未找到该学生";
            }
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
            return "删除失败：数据库操作错误";
        }
    }

    public static String DeleteTeacher(String Tno) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        String sql = "DELETE FROM Teacher WHERE Tno = '" + Tno + "'";

        try {
            int affectedRows = stmt.executeUpdate(sql);
            if (affectedRows > 0) {
                c.commit();  // 提交事务
                return "删除成功";
            } else {
                c.rollback();  // 回滚事务
                return "删除失败：未找到该教师";
            }
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
            return "删除失败：数据库操作错误";
        }
    }

    public static String UpdateStudent(String SNo, String key, String message) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        // 使用PreparedStatement以防SQL注入
        String sql = "UPDATE Student SET " + key + " = ? WHERE SNo = ?";
        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            pstmt.setString(1, message);
            pstmt.setString(2, SNo);

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                c.commit();  // 提交事务
                return "更新成功";
            } else {
                c.rollback();  // 回滚事务
                return "更新失败：未找到该学生";
            }
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
            return "更新失败：数据库操作错误";
        }
    }

    public static String UpdateTeacher(String Tno, String key, String message) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        // 使用PreparedStatement以防SQL注入
        String sql = "UPDATE Teacher SET " + key + " = ? WHERE Tno = ?";
        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            pstmt.setString(1, message);
            pstmt.setString(2, Tno);

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                c.commit();  // 提交事务
                return "更新成功";
            } else {
                c.rollback();  // 回滚事务
                return "更新失败：未找到该教师";
            }
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
            return "更新失败：数据库操作错误";
        }
    }

    public static Student queryStudent(String SNo) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        String sql = "SELECT * FROM Student WHERE SNo = ?";
        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            pstmt.setString(1, SNo);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Student student = new Student();
                    student.setSNo(rs.getString("SNo"));
                    student.setSCName(rs.getString("SCName"));
                    student.setSType(rs.getString("SType"));
                    student.setSEnter(rs.getInt("SEnter"));
                    student.setSTNo(rs.getString("STNo"));
                    student.setSATNo(rs.getString("SATNo"));
                    student.setSRemark(rs.getString("SRemark"));
                    student.setSAccount(rs.getString("SAccount"));
                    student.setSPassword(rs.getString("SPassword"));
                    student.setSIn(rs.getTimestamp("SIn"));
                    student.setSOut(rs.getTimestamp("SOut"));
                    return student;
                } else {
                    return null;
                }
            }catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    public static Teacher queryTeacher(String Tno) throws SQLException {
        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        String sql = "SELECT * FROM Teacher WHERE Tno = ?";
        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            pstmt.setString(1, Tno);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTno(rs.getString("Tno"));
                teacher.setTTno(rs.getString("TTno"));
                teacher.setTCName(rs.getString("TCName"));
                teacher.setTSex(rs.getString("TSex"));
                teacher.setJTno(rs.getString("JTno"));
                teacher.setTQualification(rs.getString("TQualification"));
                teacher.setTDuties(rs.getString("TDuties"));
                teacher.setTRemark(rs.getString("TRemark"));
                teacher.setTSituation(rs.getString("TSituation"));
                teacher.setTEducation(rs.getString("TEducation"));
                teacher.setTFirstDiscipline(rs.getString("TFirstDiscipline"));
                teacher.setTSecondDiscipline(rs.getString("TSceondDiscipline"));
                teacher.setTProfessionalAffiliations(rs.getString("TProfessionalAffiliations"));
                teacher.setTAdminPositionsCollege(rs.getString("TAdminPositionsCollege"));
                teacher.setTAcademicPositionsCollege(rs.getString("TAcademicPositionsCollege"));
                return teacher;
            } else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void insertStudentData(List<String[]> data) throws SQLException {
        String sql = "INSERT INTO Student(SNo, SCName, SType, SEnter, STNo, SATNo, SRemark, SAccount, SPassword, SIn, SOut) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            for (String[] row : data) {
                pstmt.setString(1, getValueOrNull(row, 0));
                pstmt.setString(2, getValueOrNull(row, 1));
                pstmt.setString(3, getValueOrNull(row, 2));
                pstmt.setInt(4, getValueOrZero(row, 3));
                pstmt.setString(5, getValueOrNull(row, 4));
                pstmt.setString(6, getValueOrNull(row, 5));
                pstmt.setString(7, getValueOrNull(row, 6));
                pstmt.setString(8, getValueOrNull(row, 7));
                pstmt.setString(9, getValueOrNull(row, 8));
                pstmt.setTimestamp(10, getTimestampOrNull(row, 9));
                pstmt.setTimestamp(11, getTimestampOrNull(row, 10));
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            c.commit();  // 提交事务
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
        }
    }

    public static void insertTeacherData(List<String[]> data) throws SQLException {
        String sql = "INSERT INTO Teacher(Tno, TTno, TCName, TSex, JTno, TQualification, TDuties, TRemark, TSituation, TEducation, TFirstDiscipline, TSecondDiscipline, TProfessionalAffiliations, TAdminPositionsCollege, TAcademicPositionsCollege) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        if (c == null) {
            System.out.println("数据库未连接，正在请求数据库连接");
            getConnect();
        } else {
            System.out.println("数据库已经连接");
        }

        try (PreparedStatement pstmt = c.prepareStatement(sql)) {
            for (String[] row : data) {
                pstmt.setString(1, getValueOrNull(row, 0));
                pstmt.setString(2, getValueOrNull(row, 1));
                pstmt.setString(3, getValueOrNull(row, 2));
                pstmt.setString(4, getValueOrNull(row, 3));
                pstmt.setString(5, getValueOrNull(row, 4));
                pstmt.setString(6, getValueOrNull(row, 5));
                pstmt.setString(7, getValueOrNull(row, 6));
                pstmt.setString(8, getValueOrNull(row, 7));
                pstmt.setString(9, getValueOrNull(row, 8));
                pstmt.setString(10, getValueOrNull(row, 9));
                pstmt.setString(11, getValueOrNull(row, 10));
                pstmt.setString(12, getValueOrNull(row, 11));
                pstmt.setString(13, getValueOrNull(row, 12));
                pstmt.setString(14, getValueOrNull(row, 13));
                pstmt.setString(15, getValueOrNull(row, 14));
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            c.commit();  // 提交事务
        } catch (SQLException e) {
            c.rollback();  // 回滚事务
            e.printStackTrace();
        }
    }

    private static String getValueOrNull(String[] row, int index) {
        if (index < row.length && !row[index].isEmpty()) {
            return row[index];
        } else {
            return null;
        }
    }

    private static int getValueOrZero(String[] row, int index) {
        if (index < row.length && !row[index].isEmpty()) {
            return Integer.parseInt(row[index]);
        } else {
            return 0;
        }
    }

    private static Timestamp getTimestampOrNull(String[] row, int index) {
        if (index < row.length && !row[index].isEmpty()) {
            return Timestamp.valueOf(row[index]);
        } else {
            return null;
        }
    }

    /**
     * 关闭数据库连接
     */
    public static void disConnect(){
        try {
            stmt.close();
            c.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
